﻿using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Common;
using System.Data;
using System.Text;
using System;

/// <summary>
/// 
/// </summary>
public partial class DuplicateTranslations : System.Web.UI.Page
{
    private Database db;
    private static int editButtonColumNo = 3;
    private SortedList<int, string> translationIds = new SortedList<int, string>();

    /// <summary>
    /// Handles the Load event of the Page control.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void Page_Load(object sender, EventArgs e)
    {
        if(!Page.IsPostBack) {
            if(db == null || !db.Connected) {
                db = new Database();
                db.OpenConnection();
            }
            using(Database.Command cmd = db.CreateCommand()) {
                cmd.CommandText = "SELECT DISTINCT language FROM translations";
                Database.DataAdapter adapter = new Database.DataAdapter(cmd);
                DataTable dt = new DataTable();
                dt.Columns.Add("language");
                dt.Columns.Add("englishname");
                DbDataReader reader = cmd.ExecuteReader();
                string abbrev;
                while(reader.Read()) {
                    abbrev = reader.GetString(0);
                    if(abbrev.Length > 0) {
                        dt.Rows.Add(abbrev, System.Globalization.CultureInfo.GetCultureInfo(abbrev).EnglishName);
                    }
                }
                reader.Close();
                dt.DefaultView.Sort = "englishname";
                radLanguage.DataSource = dt;
                radLanguage.DataValueField = "language";
                radLanguage.DataTextField = "englishname";
                radLanguage.DataBind();
                if(radLanguage.Items.Count > 0) {
                    radLanguage.SelectedIndex = 0;
                }
                SelectLanguage(null, EventArgs.Empty);
            }
        } else {
            if(mltMain.ActiveViewIndex == 1) {
                InitializeDataSource();
            }
        }
        Page.ClientScript.RegisterClientScriptInclude("main", ResolveUrl("~/js/main.js"));
        Page.ClientScript.RegisterClientScriptInclude("jquery", "http://ajax.googleapis.com/ajax/libs/jquery/1.2.6/jquery.min.js");
        Page.ClientScript.RegisterClientScriptInclude("json", ResolveUrl("~/js/json.js"));
        Page.ClientScript.RegisterClientScriptInclude("translationjs", ResolveUrl("~/js/translations.js"));
        Master.RenderJSArrayWithCliendIds(fullsizeholder);
    }


    /// <summary>
    /// Closes the database connection when rendering is done.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void Page_Unload(object sender, EventArgs e) {
        if(db != null && db.Connected) {
            db.CloseConnection();
        }
    }

    /// <summary>
    /// Binds the grid.
    /// </summary>
    private void BindGrid() {
        InitializeTranslationsDataSource();
        grdFindDuplicates.DataBind();
    }

    /// <summary>
    /// Selects the language.
    /// </summary>
    /// <param name="sender">The sender.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void SelectLanguage(object sender, EventArgs e) {
        if(radLanguage.SelectedItem != null) {
            BindGrid();
        }
    }

    /// <summary>
    /// Initializes the translations data source.
    /// </summary>
    private void InitializeTranslationsDataSource() {
        switch(Constants.DatabaseInstanceType) {
            case Database.DatabaseType.MySQL:
                if(radLanguage.SelectedValue.ToLower() == "all") {
                    dsTranslations.SelectCommand = "SELECT translations.translationid, translations.translation, COUNT(*) AS number FROM translations GROUP BY language, translation HAVING COUNT(*) > 1 ORDER BY translation;";
                } else {
                    dsTranslations.SelectCommand = "SELECT translations.translationid, translations.translation, COUNT(*) AS number FROM translations WHERE language = ?lang GROUP BY translation HAVING COUNT(*) > 1 ORDER BY translation;";
                }
                break;
            case Database.DatabaseType.SQLServer: // TODO: the 'all' option gives no results, need to fix
                dsTranslations.SelectCommand = @"SELECT translation,translationid,number FROM
                                                (SELECT ROW_NUMBER() OVER (PARTITION BY translation ORDER BY translationid DESC) 
                                                AS Seq,translation,translationid,
                                                COUNT(translationid) OVER (PARTITION BY translation) AS number
                                                FROM translations
                                                WHERE language = @lang
                                                )t WHERE Seq=1 AND number > 1";
                break;
        }
        if(dsTranslations.SelectParameters.Count == 0) {
            dsTranslations.SelectParameters.Add("lang", TypeCode.String, radLanguage.SelectedValue);
        } else {
            dsTranslations.SelectParameters["lang"].DefaultValue = radLanguage.SelectedValue;
        }
    }

    /// <summary>
    /// Shows the number of items on all pages of the GridView
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.Web.UI.WebControls.SqlDataSourceStatusEventArgs"/> instance containing the event data.</param>
    protected void dsTranslations_Selected(object sender, SqlDataSourceStatusEventArgs e) {
        lblNumDuplicates.Text = string.Format("{0} Duplicate Translations for {1}", e.AffectedRows, radLanguage.SelectedItem.Text);
    }

    /// <summary>
    /// Handles the RowCommand event of the grdFindDuplicates control.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.Web.UI.WebControls.GridViewCommandEventArgs"/> instance containing the event data.</param>
    protected void grdFindDuplicates_RowCommand(object sender, GridViewCommandEventArgs e) {
        BindGrid();
        switch(e.CommandName) {
            case "viewdetails":
                ViewDuplicates(e.CommandArgument.ToString());
                break;
        }
    }

    /// <summary>
    /// Handles the PageIndexChanging event of the translation grid.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.Web.UI.WebControls.GridViewPageEventArgs"/> instance containing the event data.</param>
    protected void grdFindDuplicates_PageIndexChanging(object sender, GridViewPageEventArgs e) {
        grdFindDuplicates.PageIndex = e.NewPageIndex;
        BindGrid();
    }

    /// <summary>
    /// Handles the Sorting event of the translation grid.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.Web.UI.WebControls.GridViewSortEventArgs"/> instance containing the event data.</param>
    protected void grdFindDuplicates_Sorting(object sender, GridViewSortEventArgs e) {
        string oldExpression = grdFindDuplicates.SortExpression,
               newExpression = e.SortExpression;
        if(oldExpression.IndexOf(newExpression) < 0) {
          if(oldExpression.Length > 0) {
              e.SortExpression = newExpression + "," + oldExpression;
          } else {
              e.SortExpression = newExpression;
          }
        } else {
          e.SortExpression = oldExpression;
        }
        BindGrid();
    }

    #region View Duplicates

    /// <summary>
    /// Views the duplicates.
    /// </summary>
    /// <param name="translationId">The translation id.</param>
    private void ViewDuplicates(string translationId) {
        if(db == null || !db.Connected) {
            db = new Database();
            db.OpenConnection();
        }
        using(Database.Command cmd = db.CreateCommand()) {
            cmd.CommandText = "SELECT translation FROM translations WHERE translationid = @tID AND language = @lang";
            cmd.AddParameterWithValue("@tID", translationId);
            cmd.AddParameterWithValue("@lang", radLanguage.SelectedValue.ToLower() == "all" ? "en-GB" : radLanguage.SelectedValue);
            string duplicateTranslation = cmd.ExecuteScalar().ToString();
            Session["duplicateTranslation"] = duplicateTranslation;
        }
        mltMain.ActiveViewIndex = 1;
        InitializeDataSource();
        grdDuplicateDetail.DataBind();
    }

    /// <summary>
    /// Initializes the TranslationID and main DataSources.
    /// </summary>
    private void InitializeDataSource() {
        if(db == null || !db.Connected) {
            db = new Database();
            db.OpenConnection();
        }
        using(Database.Command cmd = db.CreateCommand()) {
            cmd.CommandText = "SELECT DISTINCT language FROM translations";
            if(string.IsNullOrEmpty(dsDuplicateDetails.SelectCommand) || translationIds.Count < 5) {
                StringBuilder sqlBuffer = new StringBuilder();
                string duplicateLanguage = radLanguage.SelectedValue.ToLower() == "all" ? "en-GB" : radLanguage.SelectedValue,
                       format = string.Empty,
                       sqlTranslationId = string.Empty,
                       abbrev;
                List<string> tmpLanguages = new List<string>();
                DbDataReader reader = cmd.ExecuteReader();
                Dictionary<string, int> columnIndex = new Dictionary<string, int>();
                switch(Constants.DatabaseInstanceType) {
                    case Database.DatabaseType.MySQL:
                        format = "MAX(IF(language='{0}',translation,null)) AS '{0}',";
                        sqlTranslationId = @"SELECT DISTINCT translationid, 
                                            CAST(IF(CHAR_LENGTH(translation) < 15, 
                                                CONCAT(translationid, ' - ',translation), 
                                                CONCAT(translationid, ' - ',SUBSTRING(translation,1,12),'...')
                                                ) AS CHAR) 
                                            AS display FROM translations WHERE language = @lang ORDER BY translationid ASC";
                        break;
                    case Database.DatabaseType.SQLServer:
                        format = "MAX(CASE WHEN language = '{0}' THEN translation ELSE NULL END) AS '{0}',";
                        sqlTranslationId = @"SELECT DISTINCT translationid, display = CAST(translationid AS VARCHAR)+' - '+
                                            CASE
                                             WHEN LEN(translation) < 15 THEN translation
                                             ELSE SUBSTRING(translation,1,12)+'...'
                                            END
                                            FROM translations WHERE language = @lang ORDER BY translationid ASC";
                        break;
                }
                while(reader.Read()) {
                    abbrev = reader[0].ToString();
                    if(abbrev.Trim().Length > 0) {
                        sqlBuffer.AppendFormat(format, abbrev);
                        tmpLanguages.Add(abbrev);
                    }
                }
                reader.Close();
                tmpLanguages.Sort();
                if(radLanguage.SelectedValue.ToLower() != "all") {
                    tmpLanguages.Remove(duplicateLanguage);
                    tmpLanguages.Insert(0, duplicateLanguage);
                }
                string[] languages = tmpLanguages.ToArray();
                for(int i = 0; i < languages.Length; i++) {
                    columnIndex[languages[i]] = i + editButtonColumNo + 1;
                }
                grdDuplicateDetail.ColumnsGenerator = new LanguageColumnGenerator(languages);

                sqlBuffer.Remove(sqlBuffer.Length - 1, 1); // remove last comma
                string crossTabColumns = sqlBuffer.ToString();
                Session["crossTabColumns"] = crossTabColumns;
                Session["colIndexes"] = columnIndex;
                switch(Constants.DatabaseInstanceType) {
                    case Database.DatabaseType.MySQL:
                        dsDuplicateDetails.SelectCommand = @"SELECT translations.translationid AS tid, translationcomments.comment AS translationcomment, CAST(COUNT(elements.translationid) / (SELECT COUNT(translationid) FROM translations WHERE translationid = tid)  AS SIGNED) AS numelements, " + crossTabColumns + @"
                                                    FROM translations LEFT JOIN translationcomments ON translations.translationid = translationcomments.translationid
                                                    LEFT JOIN elements ON translations.translationid = elements.translationid
                                                    WHERE translations.translationid IN (SELECT DISTINCT translationid FROM translations WHERE language = ?lang AND translation = ?translation)
                                                    GROUP BY translations.translationid";
                        break;
                    case Database.DatabaseType.SQLServer:
                        dsDuplicateDetails.SelectCommand = @"SELECT translations.translationid AS tid, translationcomments.comment AS translationcomment, COUNT(elements.translationid) / COALESCE(NULLIF((SELECT COUNT(translations.translationid) FROM translations WHERE translations.translationid = elements.translationid), 0), 1) AS numelements, " + crossTabColumns +
                                               @"FROM translations 
                                                LEFT JOIN translationcomments ON translations.translationid = translationcomments.translationid
                                                LEFT JOIN elements ON translations.translationid = elements.translationid
                                                WHERE translations.translationid IN (SELECT DISTINCT translationid FROM translations WHERE language = @lang AND translation = @translation)
                                                GROUP BY translations.translationid,
                                                translationcomments.comment,
                                                elements.translationid";
                        
                        break;
                }
                grdDuplicateDetail.DataSource = dsDuplicateDetails;

                if(dsDuplicateDetails.SelectParameters.Count == 0) {
                    dsDuplicateDetails.SelectParameters.Add("lang", TypeCode.String, duplicateLanguage);
                    dsDuplicateDetails.SelectParameters.Add("translation", TypeCode.String, Session["duplicateTranslation"] == null ? "en-GB" : Session["duplicateTranslation"].ToString());
                } else {
                    dsDuplicateDetails.SelectParameters["lang"].DefaultValue = duplicateLanguage;
                    dsDuplicateDetails.SelectParameters["translation"].DefaultValue = Session["duplicateTranslation"] == null ? "en-GB" : Session["duplicateTranslation"].ToString();
                }
                cmd.CommandText = sqlTranslationId;
                cmd.AddParameterWithValue("@lang", duplicateLanguage);
                reader = cmd.ExecuteReader();
                translationIds.Clear();
                translationIds.Add(-1, "New Translation");
                while(reader.Read()) {
                    translationIds.Add(int.Parse(reader["translationid"].ToString()), reader["display"].ToString());
                }
                reader.Close();
            }
        }
    }

    /// <summary>
    /// Databinds the TranslationID combobox and assigns JavaScript listeners.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.Web.UI.WebControls.GridViewRowEventArgs"/> instance containing the event data.</param>
    protected void grdDuplicateDetail_RowDataBound(object sender, GridViewRowEventArgs e) {
        if(e.Row.RowType == DataControlRowType.DataRow) {
            DataRowView view = e.Row.DataItem as DataRowView;

            Label lblTranslationIdDesc = e.Row.FindControl("lblTranslationIdDesc") as Label;
            DropDownList cboTranslationId = e.Row.FindControl("cboTranslationID") as DropDownList;
            Button btnSave = e.Row.FindControl("btnSave") as Button;
            ImageButton btnDelete = e.Row.FindControl("btnDelete") as ImageButton;
            int numElements = int.Parse(view["numelements"].ToString());
            if(numElements == 0) {
                lblTranslationIdDesc.Visible = false;
                cboTranslationId.Visible = false;
                btnSave.ToolTip = "Save translation";
            } else {
                if(numElements == 1) {
                    lblTranslationIdDesc.Text = "Reassign 1 element to translation";
                } else {
                    lblTranslationIdDesc.Text = string.Format("Reassign {0} elements to translation", numElements);
                }
                cboTranslationId.DataSource = translationIds;
                cboTranslationId.DataTextField = "Value";
                cboTranslationId.DataValueField = "Key";
                cboTranslationId.DataBind();
                cboTranslationId.SelectedValue = view["tid"].ToString();
                btnSave.ToolTip = string.Format("Save translation and assign to {0} elements", numElements);
                btnDelete.ToolTip = "Translations cannot be deleted if there are elements using them";
                btnDelete.ImageUrl = ResolveUrl("~/images/delete_disabled.png");
            }

            // find the JS callback which is assigned to the 'Change Translation' button
            Button btnChangeTranslation = e.Row.FindControl("btnChangeTranslation") as Button;
            string jsChangeTranslation = ClientScript.GetPostBackClientHyperlink(btnChangeTranslation, "");
            cboTranslationId.Attributes["onchange"] = jsChangeTranslation; // assign the callback to the dropdown list so a button press is simulated when the index changes

            TextBox txtTranslationComment = (e.Row.FindControl("txtTranslationComment") as TextBox);
            if(!String.IsNullOrEmpty(txtTranslationComment.Text)) {
                e.Row.Cells[3].ToolTip = txtTranslationComment.Text;
                e.Row.Cells[3].CssClass = "ssCell commentCell";
            }
        }
    }


    /// <summary>
    /// Handles the PageIndexChanging event of the translation grid.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.Web.UI.WebControls.GridViewPageEventArgs"/> instance containing the event data.</param>
    protected void grdDuplicateDetail_PageIndexChanging(object sender, GridViewPageEventArgs e) {
        grdDuplicateDetail.PageIndex = e.NewPageIndex;
        grdDuplicateDetail.DataBind();
    }

    /// <summary>
    /// Handles the PreRender event of the grdDuplicateDetail control.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void grdDuplicateDetail_PreRender(object sender, EventArgs e) {            
        if(grdDuplicateDetail.SelectedIndex >= 0 && grdDuplicateDetail.Rows.Count > grdDuplicateDetail.SelectedIndex) {
            GridViewRow row = grdDuplicateDetail.Rows[grdDuplicateDetail.SelectedIndex];
            Label lblTranslationIdDesc = row.FindControl("lblTranslationIdDesc") as Label;
            if(lblTranslationIdDesc.Visible) { // if the row is invisible, the elements using this translation have been re-assigned and there is no need to register the AJAX search
                StringBuilder jqueryBuffer = new StringBuilder("var prm = Sys.WebForms.PageRequestManager.getInstance(); prm.add_endRequest(function() { \n");
                View view;
                TextBox txt;
                HyperLink btn;
                string rowId;
                for(int i = editButtonColumNo + 1; i < row.Cells.Count; i++) {
                    rowId = row.Cells[i].ClientID;
                    view = (row.Cells[i].Controls[0] as MultiView).Views[1];
                    txt = view.Controls[0] as TextBox;
                    btn = view.Controls[2] as HyperLink;
                    jqueryBuffer.Append("$(\"#").Append(btn.ClientID).Append("\").click(function () {\n")
                                .Append("$(\"#").Append(rowId).Append("\").find(\"#ajax-").Append(rowId).Append("\").remove();\n")
                                .Append("$(\"<img/>\").attr({src:\"images/ajax-small.gif\",alt:\"Searching...\",id:\"ajax-").Append(rowId).Append("\"}).addClass(\"ajax-small\").appendTo(\"#").Append(rowId).Append("\");\n")
                                .Append("$.ajax({type:\"POST\",url:\"TranslationFinder.asmx/FindTranslation\",contentType:\"application/json; charset=utf-8\",")
                                        .Append("data:getTranslationData('").Append(txt.ClientID).Append("','").Append(btn.NavigateUrl.Substring(1)).Append("'),success: function(response) { ")
                                            .Append("showMatchingTranslations(response, '").Append(rowId).Append("', false);")
                                        .Append("}\n")
                                .Append("});return false;\n")
                                .Append("});\n");
                }
                jqueryBuffer.Append("});");
                //System.Diagnostics.Debug.WriteLine(jqueryBuffer.ToString());
                ScriptManager.RegisterStartupScript(this, typeof(Page), "translationfinder", jqueryBuffer.ToString(), true);
            }
        }
    }

    /// <summary>
    /// Handles RowCommand events from the translation grid.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.Web.UI.WebControls.GridViewCommandEventArgs"/> instance containing the event data.</param>
    protected void grdDuplicateDetail_RowCommand(object sender, GridViewCommandEventArgs e) {
        InitializeDataSource();
        grdDuplicateDetail.DataBind();
        GridViewRow row;  // The row which has clicked was the undatabound one, so use it's index to lookup the new, databound row
        switch(e.CommandName) {
            case "edittranslation": // 'Edit' button clicked
                row = grdDuplicateDetail.Rows[((e.CommandSource as WebControl).NamingContainer as GridViewRow).RowIndex];
                row.CssClass = "ssRowSelected";
                ToggleEditControls(row, true);
                grdDuplicateDetail.SelectedIndex = row.RowIndex;
                break;
            case "canceledit":
                row = grdDuplicateDetail.Rows[((e.CommandSource as WebControl).NamingContainer as GridViewRow).RowIndex];
                ToggleEditControls(row, false);
                grdDuplicateDetail.SelectedIndex = -1;
                break;
            case "changetranslation": // index of dropdownlist changed
                row = (e.CommandSource as WebControl).NamingContainer as GridViewRow;
                string selectedTranslationID = (row.Cells[editButtonColumNo].FindControl("cboTranslationID") as DropDownList).SelectedValue;
                ChangeDisplayedTranslation(row, selectedTranslationID);
                break;
            case "savetranslation": // 'Save' button clicked
                row = (e.CommandSource as WebControl).NamingContainer as GridViewRow;
                UpdateTranslation(row, e.CommandArgument.ToString());
                grdDuplicateDetail.SelectedIndex = -1;
                break;
            case "deletetranslation":
                row = grdDuplicateDetail.Rows[((e.CommandSource as WebControl).NamingContainer as GridViewRow).RowIndex];
                row.CssClass = "ssRowSelected";
                ConfirmDelete(row, e.CommandArgument.ToString());
                break;
            case "confirmdelete":
                DeleteTranslation(e.CommandArgument.ToString());
                break;
            case "showelements":
                row = grdDuplicateDetail.Rows[((e.CommandSource as WebControl).NamingContainer as GridViewRow).RowIndex];
                ShowElements(row, e.CommandArgument.ToString());
                break;
        }
    }

    /// <summary>
    /// Handles the Sorting event of the translation grid.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.Web.UI.WebControls.GridViewSortEventArgs"/> instance containing the event data.</param>
    protected void grdDuplicateDetail_Sorting(object sender, GridViewSortEventArgs e) {
        /*      InitializeDataSource();
              InitializeGridView();
              //grdTranslations.DataBind();
              string oldExpression = grdTranslations.SortExpression,
                     newExpression = e.SortExpression;
              if(oldExpression.IndexOf(newExpression) < 0) {
                  if(oldExpression.Length > 0) {
                      e.SortExpression = newExpression + "," + oldExpression;
                  } else {
                      e.SortExpression = newExpression;
                  }
              } else {
                  e.SortExpression = oldExpression;
              }*/
        //        grdTranslations.DataBind();
    }

    /// <summary>
    /// Shows the textbox controls to edit a translation.
    /// </summary>
    /// <param name="row">The row for which to show the controls.</param>
    /// <param name="visible"><c>true</c> to show the controls, <c>false to hide them</c>.</param>
    private void ToggleEditControls(GridViewRow row, bool visible) {
        int oldRowActiveIndex = visible ? 0 : 1,
            newRowActiveIndex = visible ? 1 : 0;
        if(grdDuplicateDetail.SelectedIndex >= 0 && grdDuplicateDetail.Rows.Count > grdDuplicateDetail.SelectedIndex) {
            GridViewRow oldRow = grdDuplicateDetail.SelectedRow;
            for(int i = editButtonColumNo + 1; i < row.Cells.Count; i++) {
                (oldRow.Cells[i].Controls[0] as MultiView).ActiveViewIndex = oldRowActiveIndex;
            }
        }

        (row.FindControl("mltActions") as MultiView).ActiveViewIndex = newRowActiveIndex;
        row.Cells[2].CssClass = "ssCell"; // remove the comment indicators
        row.Cells[3].CssClass = "ssCell";
        for(int i = editButtonColumNo + 1; i < row.Cells.Count; i++) {
            (row.Cells[i].Controls[0] as MultiView).ActiveViewIndex = newRowActiveIndex;
        }
    }

    /// <summary>
    /// Changes the translation selected for an element.
    /// </summary>
    /// <param name="oldRow">The old row.</param>
    /// <param name="selectedTranslation">The new translation ID.</param>
    private void ChangeDisplayedTranslation(GridViewRow oldRow, string selectedTranslation) {
        GridViewRow row = grdDuplicateDetail.Rows[oldRow.RowIndex]; // the index of the old row in the new grid
        ToggleEditControls(row, true);
        DropDownList cboTranslationID = (row.Cells[editButtonColumNo].FindControl("cboTranslationID") as DropDownList);
        cboTranslationID.SelectedValue = selectedTranslation;
        row.CssClass = "ssRowSelected";
        int textBoxControlIndex = 0;
        if(selectedTranslation == "-1") {
            for(int i = editButtonColumNo + 1; i < row.Cells.Count; i++) {
                ((row.Cells[i].Controls[0] as MultiView).Views[1].Controls[textBoxControlIndex] as TextBox).Text = "";
            }
        } else {
            if(db == null || !db.Connected) {
                db = new Database();
                db.OpenConnection();
            }
            Dictionary<string, int> columnIndex = Session["colIndexes"] as Dictionary<string, int>;
            using(Database.Command cmd = db.CreateCommand()) {
                cmd.CommandText = "SELECT " + Session["crossTabColumns"] + " FROM translations WHERE translationid = @tID";
                cmd.AddParameterWithValue("@tID", selectedTranslation);
                DbDataReader reader = cmd.ExecuteReader();
                if(reader.HasRows) {
                    reader.Read();
                    object translation;
                    for(int i = 0; i < reader.FieldCount; i++) {
                        translation = reader[i];
                        ((row.Cells[columnIndex[reader.GetName(i).ToString()]].Controls[0] as MultiView).Views[1].Controls[textBoxControlIndex] as TextBox).Text = translation == null ? "" : translation.ToString();
                    }
                }
                reader.Close();

                cmd.CommandText = "SELECT comment FROM translationcomments WHERE translationid = @tID";
                reader = cmd.ExecuteReader();
                if(reader.HasRows) {
                    reader.Read();
                    (row.Cells[editButtonColumNo].FindControl("txtTranslationComment") as TextBox).Text = reader.GetString(0);
                } else {
                    (row.Cells[editButtonColumNo].FindControl("txtTranslationComment") as TextBox).Text = string.Empty;
                }
            }
        }
    }

    /// <summary>
    /// Updates a translation.
    /// </summary>
    /// <param name="oldRow">The old row.</param>
    /// <param name="oldId">The old id.</param>
    private void UpdateTranslation(GridViewRow oldRow, string oldId) {
        GridViewRow newRow = grdDuplicateDetail.Rows[oldRow.RowIndex];
        if(db == null || !db.Connected) {
            db = new Database();
            db.OpenConnection();
        }
        using(Database.Command cmd = db.CreateCommand()) {
            Label lblError = grdDuplicateDetail.FooterRow.Cells[editButtonColumNo].FindControl("lblError") as Label;
            string translationID = (oldRow.Cells[editButtonColumNo].FindControl("cboTranslationId") as DropDownList).SelectedValue,
                   translationComment = (oldRow.Cells[3].FindControl("txtTranslationComment") as TextBox).Text;

            if(translationID == "-1") {
                cmd.CommandText = "SELECT MAX(translationid) FROM translations";
                int maxId = 0;
                using(DbDataReader maxIdReader = cmd.ExecuteReader(CommandBehavior.SingleRow)) {
                    if(maxIdReader.HasRows) {
                        maxIdReader.Read();
                        int.TryParse(maxIdReader[0].ToString(), out maxId);
                        maxId++;
                    }
                    maxIdReader.Close();
                }
                translationID = maxId.ToString();
            }

            cmd.Parameters.Clear();
            switch(db.InstanceType) {
                case Database.DatabaseType.MySQL:
                    cmd.CommandText = "INSERT INTO translations (translationid, language, translation) VALUES (@tID, @lang, @trans) ON DUPLICATE KEY UPDATE translation = @trans";
                    break;
                case Database.DatabaseType.SQLServer:
                    cmd.CommandText = "UPDATE translations SET translation = @trans WHERE translationid = @tID AND language = @lang; IF @@ROWCOUNT = 0 INSERT INTO translations (translationid, language, translation) VALUES (@tID, @lang, @trans)";
                    break;
            }
            cmd.AddParameterWithValue("@tID", translationID);
            cmd.AddParameterWithValue("@trans", "");
            cmd.AddParameterWithValue("@lang", "");
            string textBoxID;
            for(int i = editButtonColumNo + 1; i < newRow.Cells.Count; i++) {
                textBoxID = (newRow.Cells[i].Controls[0] as MultiView).Views[1].Controls[0].UniqueID;
                foreach(string key in Request.Form.AllKeys) {
                    if(key == textBoxID && Request.Form[key].Length > 0) {
                        cmd.Parameters["@trans"].Value = Request.Form[key];
                        cmd.Parameters["@lang"].Value = textBoxID.Substring(textBoxID.LastIndexOf("$") + 1); // the ID of the textbox has $country-CODE appended to it
                        cmd.ExecuteNonQuery();
                        break;
                    }
                }
            }
            if(translationComment.Length > 0) {
                cmd.Parameters.Clear();
                switch(db.InstanceType) {
                    case Database.DatabaseType.MySQL:
                        cmd.CommandText = "INSERT INTO translationcomments (translationid, comment) VALUES (@tID, @comment) ON DUPLICATE KEY UPDATE comment = @comment";
                        break;
                    case Database.DatabaseType.SQLServer:
                        cmd.CommandText = "UPDATE translationcomments SET comment = @comment WHERE translationid = @tID; IF @@ROWCOUNT = 0 INSERT INTO translationcomments (translationid, comment) VALUES (@tID, @comment)";
                        break;
                }
                cmd.AddParameterWithValue("@tID", translationID);
                cmd.AddParameterWithValue("@comment", translationComment);
                cmd.ExecuteNonQuery();
            }
            cmd.Parameters.Clear();
            cmd.CommandText = "UPDATE elements SET translationid = @tID WHERE translationid = @oldTId";
            cmd.AddParameterWithValue("@tID", translationID);
            cmd.AddParameterWithValue("@oldTId", oldId);
            try {
                cmd.ExecuteNonQuery();
            } catch(Exception ex) {
                lblError.Text = "Error: "+ex.Message;
                lblError.Visible = true;
            }
        }
        grdDuplicateDetail.DataBind();
    }

    /// <summary>
    /// Confirms the delete.
    /// </summary>
    /// <param name="row">The row.</param>
    /// <param name="translationId">The translation id.</param>
    private void ConfirmDelete(GridViewRow row, string translationId) {
        if(db == null || !db.Connected) {
            db = new Database();
            db.OpenConnection();
        }
        using(Database.Command cmd = db.CreateCommand()) {
            cmd.CommandText = "SELECT COUNT(*) FROM elements WHERE translationid = @tId";
            cmd.AddParameterWithValue("@tId", translationId);
            int numElements = 0;
            System.Diagnostics.Debug.WriteLine(translationId+"-"+ cmd.ExecuteScalar());
            int.TryParse(cmd.ExecuteScalar().ToString(), out numElements);
            if(numElements > 0) {
                ToggleEditControls(row, true);
                grdDuplicateDetail.SelectedIndex = row.RowIndex;
                Label lblError = row.FindControl("lblError") as Label;
                lblError.Visible = true;
                lblError.Text = "Translations cannot be deleted if there are elements using them";
            } else {
                ToggleEditControls(row, false);
                (row.FindControl("mltActions") as MultiView).ActiveViewIndex = 2;
            }
        }
    }

    /// <summary>
    /// Deletes the translation.
    /// </summary>
    /// <param name="translationId">The translation id.</param>
    private void DeleteTranslation(string translationId) {
        if(db == null || !db.Connected) {
            db = new Database();
            db.OpenConnection();
        }
        using(Database.Command cmd = db.CreateCommand()) {
            cmd.CommandText = "DELETE FROM translations WHERE translationid = @tId";
            cmd.AddParameterWithValue("@tId", translationId);
            cmd.ExecuteNonQuery();
        }
        grdDuplicateDetail.DataBind();
    }

    /// <summary>
    /// Shows the elements.
    /// </summary>
    /// <param name="newRow">The new row.</param>
    /// <param name="visible">1 if the elements panel should be visible, otherwise false.</param>
    private void ShowElements(GridViewRow newRow, string visible) {
        Panel pnlElementDetail = newRow.FindControl("pnlElementDetail") as Panel;
        if(visible == "1") {
            if(db == null || !db.Connected) {
                db = new Database();
                db.OpenConnection();
            }
            ImageButton imgShowDetails = newRow.FindControl("imgShowDetails") as ImageButton;
            imgShowDetails.ImageUrl = ResolveUrl("~/images/arrow_up.gif");
            imgShowDetails.CommandArgument = "0";
            imgShowDetails.ToolTip = "Hide elements";
            using(Database.Command cmd = db.CreateCommand()) {
                cmd.CommandText = "SELECT a.displayname AS app, b.name AS bundle, e.elementname AS element FROM elements AS e INNER JOIN bundles as b ON bundle = b.id INNER JOIN applications AS a ON a.id = b.application WHERE translationid = @tId ORDER BY displayname, bundle, elementname";
                cmd.AddParameterWithValue("@tId", (newRow.FindControl("lblTranslationId") as Label).Text);
                DbDataReader reader = cmd.ExecuteReader();
                SortedDictionary<string, SortedDictionary<string, List<string>>> elements = new SortedDictionary<string, SortedDictionary<string, List<string>>>(); // application, bundle, element
                string app,
                       bundle,
                       element,
                       lastApp = string.Empty,
                       lastBundle = string.Empty,
                       lastElement = string.Empty;
                BulletedList lst = null;
                while(reader.Read()) {
                    app = reader["app"].ToString();
                    bundle = reader["bundle"].ToString();
                    element = reader["element"].ToString();
                    if(app != lastApp) {
                        Literal appHeading = new Literal();
                        appHeading.Text = "<strong>" + app + "</strong>";
                        pnlElementDetail.Controls.Add(appHeading);
                        lst = new BulletedList();
                        pnlElementDetail.Controls.Add(lst);
                        lastApp = app;
                    }
                    lst.Items.Add(new ListItem(bundle + " - " + element));
                }
                pnlElementDetail.Visible = true;
            }
        }
    }

    /// <summary>
    /// Switches back to the first form, retaining the selected values.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void lnkNavDuplicateTranslations_Click(object sender, EventArgs e) {
        mltMain.ActiveViewIndex = 0;
        BindGrid();
    }

    /// <summary>
    /// Expands/contracts the grid to fill the whole window
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void lnkExpandScreen_Click(object sender, EventArgs e) {
        if(fullsizeholder.Attributes["class"] == "ssFullSize") {
            lnkExpandScreen.Text = "Expand to fill window";
            lnkExpandScreen.CssClass = "lnkExpand";
            fullsizeholder.Attributes["class"] = "ssContracted";
        } else {
            lnkExpandScreen.Text = "Contract to normal size";
            lnkExpandScreen.CssClass = "lnkContract";
            fullsizeholder.Attributes["class"] = "ssFullSize";
        }
    }

    #endregion
}
